﻿using EmoneyWeiXinCommon;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace EmoneyWeiXinDAL
{
    public class CreateReportByModelDAL
    {
        public static DataTable GetDaShiJianData()
        {
            string sql = string.Format(@"select a.*,b.f006v_12006 from EMBASE.embase2.dbo.vm_dms_getnewsbycode_12 as a
                                        INNer JOIN EMBASE.embase2.dbo.tb_news_12006 as b
                                        ON a.newsid_12001=b.newsid_12006
                                        where  
                                        a.rectime>='{0}'
                                        AND code IN('6010102'--板块
                                        ,'6080104'--市场新闻
                                        ,'6080102'--A股市场
                                        ,'6060103'--国内
                                        ,'6050203'--全球股市
                                        ,'6060202'--区域经济
                                        ,'6050102'--国际
                                        )
                                        AND f003v_12001='1' order by f004v_12001 desc", System.DateTime.Now.ToString("yyyy-MM-dd"));
            return SQLHelper.ExecuteGetTable(Config.Conn30, CommandType.Text, sql, null);
        }

        public static DataTable GetNeiMuChuanwen()
        {
            string sql = string.Format(@"select f021v_12001,f006v_12001 from EMBASE.embase2.dbo.vm_dms_getnewsbycode_12 
                                         where F002V_12001 IN ('投资者关系互动新闻')
                                         AND rectime>='{0}'
                                         and valid = 1", System.DateTime.Now.ToString("yyyy-MM-dd"));
            return SQLHelper.ExecuteGetTable(Config.Conn30, CommandType.Text, sql, null);
        }

        //机构调研
        public static DataTable GetJigouDiaoYan()
        {
            string sql = string.Format(@"Select * from RES_STOCK_SUM
                                         where rectime>'{0} 8:10:00'  AND rectime<'{1} 8:10:00'
                                        order by ActOrgNum desc, ActionDate desc", System.DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"), System.DateTime.Now.ToString("yyyy-MM-dd"));
            return SQLHelper.ExecuteGetTable(Config.Conn30, CommandType.Text, sql, null);
        }

        public static DataSet GetZhongDaLiHao()
        {
            string sql = string.Format(@"select * from EMmidd2.dbo.VM_HOTOPIC_BULLSTOCKS 
                                         where RecTime>='{0}';
                                         Select CompanyRecords,SumNewsRecords  , IncNewsRecords  
                                         from  EMmidd2.dbo.HOTOPIC_BULLSTOCKS_STAT where RecTime>'{0}';", System.DateTime.Now.ToString("yyyy-MM-dd"));
            return SQLHelper.ExecuteGetDataSet(Config.Conn35, CommandType.Text, sql, null);
        }

        public static DataTable GetLongHubang()
        {
            string sql = string.Format(@"select secuCode,tb1.secubrief,SalesDepartName,BuySum,SaleSum,ISNULL( BuySum,0)-ISNULL( SaleSum,0) jmSum  from ABNORMAL_INFO_GRAB tb1
INNER JOIN
 (
      select top 10 secubrief from ABNORMAL_INFO_GRAB 
      where tradeDate>='{0}'
      group by secubrief
 ) tb ON tb1.secubrief=tb.secubrief
where  
tradeDate>='{0}'
order by secubrief,jmSum desc", System.DateTime.Now.ToString("yyyy-MM-dd"));
            return SQLHelper.ExecuteGetTable(Config.Transfer, CommandType.Text, sql, null);
        }

    }
}
